package com.school.dao;

import com.school.entity.Class;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * 班级DAO
 */
@Repository
public class ClassDao {
    
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    
    /**
     * 插入班级
     */
    public Long insert(Class clazz) {
        String sql = "INSERT INTO tb_class (class_name, teacher_name, phone, major, student_count, status, remark) " +
                "VALUES (:className, :teacherName, :phone, :major, :studentCount, :status, :remark)";
        
        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("className", clazz.getClassName())
                .addValue("teacherName", clazz.getTeacherName())
                .addValue("phone", clazz.getPhone())
                .addValue("major", clazz.getMajor())
                .addValue("studentCount", clazz.getStudentCount())
                .addValue("status", clazz.getStatus())
                .addValue("remark", clazz.getRemark());
        
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql, params, keyHolder);
        return keyHolder.getKey().longValue();
    }
    
    /**
     * 更新班级
     */
    public int update(Class clazz) {
        String sql = "UPDATE tb_class SET teacher_name = :teacherName, phone = :phone, major = :major, " +
                "student_count = :studentCount, status = :status, remark = :remark WHERE id = :id";
        
        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("id", clazz.getId())
                .addValue("teacherName", clazz.getTeacherName())
                .addValue("phone", clazz.getPhone())
                .addValue("major", clazz.getMajor())
                .addValue("studentCount", clazz.getStudentCount())
                .addValue("status", clazz.getStatus())
                .addValue("remark", clazz.getRemark());
        
        return namedParameterJdbcTemplate.update(sql, params);
    }
    
    /**
     * 删除班级
     */
    public int deleteById(Long id) {
        String sql = "DELETE FROM tb_class WHERE id = :id";
        MapSqlParameterSource params = new MapSqlParameterSource("id", id);
        return namedParameterJdbcTemplate.update(sql, params);
    }
    
    /**
     * 批量删除班级
     */
    public int batchDelete(List<Long> ids) {
        String sql = "DELETE FROM tb_class WHERE id IN (:ids)";
        MapSqlParameterSource params = new MapSqlParameterSource("ids", ids);
        return namedParameterJdbcTemplate.update(sql, params);
    }
    
    /**
     * 根据ID查询
     */
    public Class findById(Long id) {
        String sql = "SELECT * FROM tb_class WHERE id = :id";
        MapSqlParameterSource params = new MapSqlParameterSource("id", id);
        List<Class> list = namedParameterJdbcTemplate.query(sql, params, 
                (rs, rowNum) -> {
                    Class c = new Class();
                    c.setId(rs.getLong("id"));
                    c.setClassName(rs.getString("class_name"));
                    c.setTeacherName(rs.getString("teacher_name"));
                    c.setPhone(rs.getString("phone"));
                    c.setMajor(rs.getString("major"));
                    c.setStudentCount(rs.getInt("student_count"));
                    c.setStatus(rs.getString("status"));
                    c.setRemark(rs.getString("remark"));
                    c.setCreateTime(rs.getTimestamp("create_time"));
                    c.setUpdateTime(rs.getTimestamp("update_time"));
                    return c;
                });
        return list.isEmpty() ? null : list.get(0);
    }
    
    /**
     * 根据班级名称查询
     */
    public Class findByClassName(String className) {
        String sql = "SELECT * FROM tb_class WHERE class_name = :className";
        MapSqlParameterSource params = new MapSqlParameterSource("className", className);
        List<Class> list = namedParameterJdbcTemplate.query(sql, params, 
                (rs, rowNum) -> {
                    Class c = new Class();
                    c.setId(rs.getLong("id"));
                    c.setClassName(rs.getString("class_name"));
                    c.setTeacherName(rs.getString("teacher_name"));
                    c.setPhone(rs.getString("phone"));
                    c.setMajor(rs.getString("major"));
                    c.setStudentCount(rs.getInt("student_count"));
                    c.setStatus(rs.getString("status"));
                    c.setRemark(rs.getString("remark"));
                    c.setCreateTime(rs.getTimestamp("create_time"));
                    c.setUpdateTime(rs.getTimestamp("update_time"));
                    return c;
                });
        return list.isEmpty() ? null : list.get(0);
    }
    
    /**
     * 查询所有班级
     */
    public List<Class> findAll() {
        String sql = "SELECT * FROM tb_class ORDER BY create_time DESC";
        return namedParameterJdbcTemplate.query(sql, 
                (rs, rowNum) -> {
                    Class c = new Class();
                    c.setId(rs.getLong("id"));
                    c.setClassName(rs.getString("class_name"));
                    c.setTeacherName(rs.getString("teacher_name"));
                    c.setPhone(rs.getString("phone"));
                    c.setMajor(rs.getString("major"));
                    c.setStudentCount(rs.getInt("student_count"));
                    c.setStatus(rs.getString("status"));
                    c.setRemark(rs.getString("remark"));
                    c.setCreateTime(rs.getTimestamp("create_time"));
                    c.setUpdateTime(rs.getTimestamp("update_time"));
                    return c;
                });
    }
    
    /**
     * 分页查询
     */
    public List<Class> findByPage(Map<String, Object> params) {
        StringBuilder sql = new StringBuilder("SELECT * FROM tb_class WHERE 1=1");
        
        if (params.get("className") != null && !"".equals(params.get("className"))) {
            sql.append(" AND class_name LIKE CONCAT('%', :className, '%')");
        }
        if (params.get("major") != null && !"".equals(params.get("major"))) {
            sql.append(" AND major = :major");
        }
        if (params.get("status") != null && !"".equals(params.get("status"))) {
            sql.append(" AND status = :status");
        }
        
        sql.append(" ORDER BY create_time DESC LIMIT :limit OFFSET :offset");
        
        return namedParameterJdbcTemplate.query(sql.toString(), params, 
                (rs, rowNum) -> {
                    Class c = new Class();
                    c.setId(rs.getLong("id"));
                    c.setClassName(rs.getString("class_name"));
                    c.setTeacherName(rs.getString("teacher_name"));
                    c.setPhone(rs.getString("phone"));
                    c.setMajor(rs.getString("major"));
                    c.setStudentCount(rs.getInt("student_count"));
                    c.setStatus(rs.getString("status"));
                    c.setRemark(rs.getString("remark"));
                    c.setCreateTime(rs.getTimestamp("create_time"));
                    c.setUpdateTime(rs.getTimestamp("update_time"));
                    return c;
                });
    }
    
    /**
     * 统计总数
     */
    public long count(Map<String, Object> params) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM tb_class WHERE 1=1");
        
        if (params.get("className") != null && !"".equals(params.get("className"))) {
            sql.append(" AND class_name LIKE CONCAT('%', :className, '%')");
        }
        if (params.get("major") != null && !"".equals(params.get("major"))) {
            sql.append(" AND major = :major");
        }
        if (params.get("status") != null && !"".equals(params.get("status"))) {
            sql.append(" AND status = :status");
        }
        
        Long count = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Long.class);
        return count != null ? count : 0L;
    }
}


